﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using Fintech.Utils;

namespace ExcelPriceParser
{
    class Program
    {
        private class Item
        {
            public Item()
            {
                NeedToInsert = true;
            }
            public bool NeedToInsert { get; set; }
        }
        private class Material : Item
        {
            public Guid Id { get; set; }
            public string Name { get; set; }

            public override int GetHashCode()
            {
                return Name.GetHashCode();
            }

            public override string ToString()
            {
                return Name;
            }
        }
        private class ProductType : Item
        {
            public ProductType()
            {
                Property1Name = string.Empty;
                Property2Name = string.Empty;
            }
            public Guid Id { get; set; }
            public string Name { get; set; }
            public string Property1Name { get; set; }
            public string Property2Name { get; set; }
            public string UnitName { get; set; }
            public Guid ParentTypeId { get; set; }

            public override int GetHashCode()
            {
                return Name.GetHashCode();
            }

            public override string ToString()
            {
                return Name;
            }
        }
        private class Product : Item
        {
            public Product()
            {
                Property1 = string.Empty;
                Property2 = string.Empty;
                Description = string.Empty;
            }

            public Guid Id { get; set; }
            public string Name { get; set; }
            public ProductType Type { get; set; }
            public string Property1 { get; set; }
            public string Property2 { get; set; }
            public string Description { get; set; }
            public string Producer { get; set;}

            public override int GetHashCode()
            {
                int result = Name.GetHashCode();
                if (Type != null)
                    result = result ^ Type.GetHashCode();
                if(Property1 != null)
                    result = result ^ Property1.GetHashCode();
                if(Property2 != null)
                    result = result ^ Property2.GetHashCode();
                if(Producer != null)
                    result = result ^ Producer.GetHashCode();

                return result;
            }

            public override string ToString()
            {
                return Name;
            }
        }
        private class PriceItem : Item
        {
            public Guid Id { get; set; }
            public Material Material { get; set; }
            public Product Product { get; set; }
            public string Color { get; set; }
            public double Cost { get; set; }

            public override string ToString()
            {
                return string.Format("{0}-{1}-{2}-{3}",
                    Product != null ? Product.ToString() : string.Empty,
                    Material != null ? Material.ToString() : string.Empty,
                    Color, Cost);
            }
        }

        static void Main(string[] args)
        {
            //ProcessFirstList();
            //ProcessSecondList();
            //ProcessThirdList();
            //ProcessForthList();
            //ProcessFifthList();
            //ProcessSixthList();
            //ProcessSeventhList();
            //ProcessEighthList();
            //ProcessNinthList();
            //ProcessTenthList();
            //ProcessEleventhList();
        }

        private static void ProcessFirstList()
        {
            Dictionary<string, Material> materials = new Dictionary<string, Material>();
            Dictionary<string, ProductType> productTypes = new Dictionary<string, ProductType>();
            Dictionary<string, Product> products = new Dictionary<string, Product>();

            var fileName = string.Format("{0}\\abc.xls", Directory.GetCurrentDirectory());
            var fileConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", fileName);

            DataTable priceTable;
            IEnumerable<DataRow> priceRows;
            using (var fileAdapter = new OleDbDataAdapter("SELECT * FROM [Price$]", fileConnectionString))
            {
                var fileDs = new DataSet();

                fileAdapter.Fill(fileDs, "Price");

                priceTable = fileDs.Tables["Price"];
                priceRows = priceTable.AsEnumerable();
            }

            // Populate materials
            priceTable.Columns.Cast<DataColumn>().Where(c => c.ColumnName != string.Empty && c.ColumnName != "F1").ForEach(c => materials.Add(c.ColumnName, new Material(){Id = Guid.NewGuid(), Name = c.ColumnName}));

            // Populate product types and products
            ProductType currentType = null;
            foreach (var priceRow in priceRows)
            {
                string name = (string) priceRow[0];
                if (priceRow[1] == DBNull.Value)
                {
                    if(!productTypes.TryGetValue(name, out currentType))
                    {
                        currentType = new ProductType() {Id = Guid.NewGuid(), Name = name};
                        productTypes.Add(name, currentType);
                    }
                }
                else
                {
                    var product = new Product() { Id = Guid.NewGuid(), Name = name, Type = currentType };
                    products.Add(name, product);
                }
            }


            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
            connectionStringBuilder.DataSource = "(local)";// \\SQLEXPRESS";
            connectionStringBuilder.InitialCatalog = "ZOVNP";
            connectionStringBuilder.IntegratedSecurity = true;
            SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());
            
            SqlTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                SqlCommand command;
                foreach (var material in materials)
                {
                    command = new SqlCommand("INSERT INTO [ZOVNP].[dbo].[Material] ([ID],[Name],[Description]) VALUES (@id,@name,@description)", connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", material.Value.Id);
                    command.Parameters.AddWithValue("@name", material.Value.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.ExecuteNonQuery();
                }

                Guid priceId = Guid.NewGuid();
                command = new SqlCommand("INSERT INTO [ZOVNP].[dbo].[Price] ([ID],[Name],[Description]) VALUES (@id,@name,@description)", connection);
                command.Transaction = transaction;
                command.Parameters.AddWithValue("@id", priceId);
                command.Parameters.AddWithValue("@name", "Прайс 1");
                command.Parameters.AddWithValue("@description", "");
                command.ExecuteNonQuery();

                foreach (var productType in productTypes)
                {
                    command = new SqlCommand("INSERT INTO [ZOVNP].[dbo].[ProductType] ([ID],[Name],[Description],[UnitName],[Property1Name],[Property2Name],[Property3Name]) VALUES (@id, @name, @description, @unitName, '', '', '')", connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", productType.Value.Id);
                    command.Parameters.AddWithValue("@name", productType.Value.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.Parameters.AddWithValue("@unitName", "");
                    command.ExecuteNonQuery();
                }

                foreach (var product in products)
                {
                    command = new SqlCommand("INSERT INTO [ZOVNP].[dbo].[Product] ([ID],[Name],[Description],[ProductType_ID],[Producer],[Property1],[Property2],[Property3]) VALUES (@id, @name, @description, @productType, @producer, '', '', '')", connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", product.Value.Id);
                    command.Parameters.AddWithValue("@name", product.Value.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.Parameters.AddWithValue("@productType", product.Value.Type.Id);
                    command.Parameters.AddWithValue("@producer", "");
                    command.ExecuteNonQuery();
                }

                foreach (var priceRow in priceRows)
                {
                    if (priceRow[1] != DBNull.Value)
                    {
                        string productName = (string) priceRow[0];
                        var product = products[productName];

                        foreach (var material in materials)
                        {
                            command =
                                new SqlCommand(
                                    "INSERT INTO [ZOVNP].[dbo].[PriceItem] ([ID],[Price_ID],[Product_ID],[Material_ID],[Color],[Cost]) VALUES (@id, @priceId, @productId, @materialId, @color, @cost)",
                                    connection);
                            command.Transaction = transaction;
                            command.Parameters.AddWithValue("@id", Guid.NewGuid());
                            command.Parameters.AddWithValue("@priceId", priceId);
                            command.Parameters.AddWithValue("@productId", product.Id);
                            command.Parameters.AddWithValue("@materialId", material.Value.Id);
                            command.Parameters.AddWithValue("@color", "");
                            command.Parameters.AddWithValue("@cost", priceRow.Field<Double>(material.Value.Name));
                            command.ExecuteNonQuery();
                        }
                    }
                }

                transaction.Commit();
            }
            catch (Exception exc)
            {
                if (transaction != null)
                    transaction.Rollback();
            }
            finally
            {
                if (transaction != null)
                    transaction.Dispose();
                connection.Close();
            }
        }

        private static void ProcessSecondList()
        {
            Dictionary<string, Material> materials = new Dictionary<string, Material>();
            Dictionary<string, Product> products = new Dictionary<string, Product>();

            var fileName = string.Format("{0}\\abc.xls", Directory.GetCurrentDirectory());
            var fileConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", fileName);

            DataTable priceTable;
            IEnumerable<DataRow> priceRows;
            using (var fileAdapter = new OleDbDataAdapter("SELECT * FROM [PriceA$]", fileConnectionString))
            {
                var fileDs = new DataSet();

                fileAdapter.Fill(fileDs, "PriceA");

                priceTable = fileDs.Tables["PriceA"];
                priceRows = priceTable.AsEnumerable();
            }

            Dictionary<string, List<PriceItem>> priceItemsPrototypes = new Dictionary<string, List<PriceItem>>();
            List<PriceItem> priceItems = new List<PriceItem>();
            ProductType productType = new ProductType() {Id = Guid.NewGuid(), Name = "Стол"};

            // Populate materials
            foreach (var priceRow in priceRows)
            {
                if (priceRow[0] != DBNull.Value && (string)priceRow[0] != string.Empty)
                {
                    // Row with product and costs
                    Product product = new Product() {Id = Guid.NewGuid(), Name = priceRow[0].ToString(), Type = productType};
                    products.Add(product.Name, product);
                    for (int i = 1; i < priceTable.Columns.Count; ++i)
                    {
                        double cost = double.Parse((string) priceRow[i]);
                        PriceItem priceItem = new PriceItem() {Id = Guid.NewGuid(), Cost = cost, Product = product};
                        string columnName = priceTable.Columns[i].ColumnName;
                        if(!priceItemsPrototypes.ContainsKey(columnName))
                            priceItemsPrototypes.Add(columnName, new List<PriceItem>());
                        priceItemsPrototypes[columnName].Add(priceItem);
                    }
                }
                else
                {
                    if (priceRow.ItemArray.Any(c => c != DBNull.Value))
                    {
                        // Row with materials
                        for (int i = 1; i < priceTable.Columns.Count; ++i)
                        {
                            if (priceRow[i] != DBNull.Value)
                            {
                                var material = new Material() {Id = Guid.NewGuid(), Name = (string) priceRow[i]};
                                materials.Add(material.Name, material);
                                var priceItemPrototypesForMaterial = priceItemsPrototypes[priceTable.Columns[i].ColumnName];
                                foreach (var priceItemPrototype in priceItemPrototypesForMaterial)
                                {
                                    priceItems.Add(new PriceItem()
                                                       {
                                                           Id = Guid.NewGuid(),
                                                           Cost = priceItemPrototype.Cost,
                                                           Product = priceItemPrototype.Product,
                                                           Material = material
                                                       });
                                }
                            }
                        }
                    }
                }
            }

            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
            connectionStringBuilder.DataSource = "(local)\\SQLEXPRESS";
            connectionStringBuilder.InitialCatalog = "ZOVNP";
            connectionStringBuilder.IntegratedSecurity = true;
            SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());

            SqlTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                SqlCommand command;
                foreach (var material in materials)
                {
                    command = new SqlCommand("INSERT INTO [ZOVNP].[dbo].[Material] ([ID],[Name],[Description]) VALUES (@id,@name,@description)",
                                             connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", material.Value.Id);
                    command.Parameters.AddWithValue("@name", material.Value.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.ExecuteNonQuery();
                }

                Guid priceId = Guid.NewGuid();
                command = new SqlCommand("INSERT INTO [ZOVNP].[dbo].[Price] ([ID],[Name],[Description]) VALUES (@id,@name,@description)", connection);
                command.Transaction = transaction;
                command.Parameters.AddWithValue("@id", priceId);
                command.Parameters.AddWithValue("@name", "Прайс 1");
                command.Parameters.AddWithValue("@description", "");
                command.ExecuteNonQuery();

                command =
                    new SqlCommand(
                        "INSERT INTO [ZOVNP].[dbo].[ProductType] ([ID],[Name],[Description],[UnitName],[Property1Name],[Property2Name],[Property3Name]) VALUES (@id, @name, @description, @unitName, '', '', '')",
                        connection);
                command.Transaction = transaction;
                command.Parameters.AddWithValue("@id", productType.Id);
                command.Parameters.AddWithValue("@name", productType.Name);
                command.Parameters.AddWithValue("@description", "");
                command.Parameters.AddWithValue("@unitName", "");
                command.ExecuteNonQuery();

                foreach (var product in products)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[Product] ([ID],[Name],[Description],[ProductType_ID],[Producer],[Property1],[Property2],[Property3]) VALUES (@id, @name, @description, @productType, @producer, '', '', '')",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", product.Value.Id);
                    command.Parameters.AddWithValue("@name", product.Value.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.Parameters.AddWithValue("@productType", product.Value.Type.Id);
                    command.Parameters.AddWithValue("@producer", "");
                    command.ExecuteNonQuery();
                }

                foreach (var priceItem in priceItems)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[PriceItem] ([ID],[Price_ID],[Product_ID],[Material_ID],[Color],[Cost]) VALUES (@id, @priceId, @productId, @materialId, @color, @cost)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", priceItem.Id);
                    command.Parameters.AddWithValue("@priceId", priceId);
                    command.Parameters.AddWithValue("@productId", priceItem.Product.Id);
                    command.Parameters.AddWithValue("@materialId", priceItem.Material.Id);
                    command.Parameters.AddWithValue("@color", "");
                    command.Parameters.AddWithValue("@cost", priceItem.Cost);
                    command.ExecuteNonQuery();
                }

                transaction.Commit();
            }
            catch (Exception exc)
            {
                if (transaction != null)
                    transaction.Rollback();
            }
            finally
            {
                if (transaction != null)
                    transaction.Dispose();
                connection.Close();
            }
        }

        private static void ProcessThirdList()
        {
            Dictionary<string, Material> materials = new Dictionary<string, Material>();
            List<Product> products = new List<Product>();

            var fileName = string.Format("{0}\\abc.xls", Directory.GetCurrentDirectory());
            var fileConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", fileName);

            DataTable priceTable;
            IEnumerable<DataRow> priceRows;
            using (var fileAdapter = new OleDbDataAdapter("SELECT * FROM [PriceB$]", fileConnectionString))
            {
                var fileDs = new DataSet();

                fileAdapter.Fill(fileDs, "PriceB");

                priceTable = fileDs.Tables["PriceB"];
                priceRows = priceTable.AsEnumerable();
            }

            List<PriceItem> priceItems = new List<PriceItem>();
            ProductType productType = new ProductType()
                                          {Id = Guid.NewGuid(), Name = "Ручки", Property1Name = "Межосевое расстояние, мм", Property2Name = "???"};

            // Populate materials
            foreach (var priceRow in priceRows)
            {
                string itemName = (string) priceRow[0];
                string materialName = (string) priceRow[1];
                string color = (string) priceRow[2];
                string property1 = priceRow[3].ToString();
                string property2 = priceRow[4].ToString();
                double cost = double.Parse(priceRow[5].ToString());
                string description = priceRow[6].ToString();

                Material material = null;
                if (!materials.ContainsKey(materialName))
                {
                    material = new Material() {Id = Guid.NewGuid(), Name = materialName};
                    materials.Add(material.Name, material);
                }
                else
                    material = materials[materialName];

                Product product = new Product()
                                      {
                                          Id = Guid.NewGuid(),
                                          Name = itemName,
                                          Property1 = property1,
                                          Property2 = property2,
                                          Type = productType,
                                          Description = description
                                      };
                var existingProduct = products.FirstOrDefault(p => p.GetHashCode() == product.GetHashCode());
                if (existingProduct == null)
                    products.Add(product);
                else
                    product = existingProduct;

                PriceItem priceItem = new PriceItem() {Id = Guid.NewGuid(), Color = color, Cost = cost, Material = material, Product = product};
                priceItems.Add(priceItem);
            }

            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
            connectionStringBuilder.DataSource = "(local)\\SQLEXPRESS";
            connectionStringBuilder.InitialCatalog = "ZOVNP";
            connectionStringBuilder.IntegratedSecurity = true;
            SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());

            SqlTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                SqlCommand command;
                foreach (var material in materials)
                {
                    command = new SqlCommand("INSERT INTO [ZOVNP].[dbo].[Material] ([ID],[Name],[Description]) VALUES (@id,@name,@description)",
                                             connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", material.Value.Id);
                    command.Parameters.AddWithValue("@name", material.Value.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.ExecuteNonQuery();
                }

                command =
                    new SqlCommand(
                        "INSERT INTO [ZOVNP].[dbo].[ProductType] ([ID],[Name],[Description],[UnitName],[Property1Name],[Property2Name],[Property3Name]) VALUES (@id, @name, @description, @unitName, @property1Name, @property2Name, @property3Name)",
                        connection);
                command.Transaction = transaction;
                command.Parameters.AddWithValue("@id", productType.Id);
                command.Parameters.AddWithValue("@name", productType.Name);
                command.Parameters.AddWithValue("@description", "");
                command.Parameters.AddWithValue("@unitName", "");
                command.Parameters.AddWithValue("@property1Name", productType.Property1Name);
                command.Parameters.AddWithValue("@property2Name", productType.Property2Name);
                command.Parameters.AddWithValue("@property3Name", "");
                command.ExecuteNonQuery();

                foreach (var product in products)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[Product] ([ID],[Name],[Description],[ProductType_ID],[Producer],[Property1],[Property2],[Property3]) VALUES (@id, @name, @description, @productType, @producer, @property1, @property2, @property3)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", product.Id);
                    command.Parameters.AddWithValue("@name", product.Name);
                    command.Parameters.AddWithValue("@description", product.Description);
                    command.Parameters.AddWithValue("@productType", product.Type.Id);
                    command.Parameters.AddWithValue("@producer", "");
                    command.Parameters.AddWithValue("@property1", product.Property1);
                    command.Parameters.AddWithValue("@property2", product.Property2);
                    command.Parameters.AddWithValue("@property3", "");
                    command.ExecuteNonQuery();
                }

                foreach (var priceItem in priceItems)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[PriceItem] ([ID],[Price_ID],[Product_ID],[Material_ID],[Color],[Cost]) VALUES (@id, @priceId, @productId, @materialId, @color, @cost)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", priceItem.Id);
                    command.Parameters.AddWithValue("@priceId", new Guid("79A01695-098F-43D0-B3C6-731F398ED0F5"));
                    command.Parameters.AddWithValue("@productId", priceItem.Product.Id);
                    command.Parameters.AddWithValue("@materialId", priceItem.Material.Id);
                    command.Parameters.AddWithValue("@color", priceItem.Color);
                    command.Parameters.AddWithValue("@cost", priceItem.Cost);
                    command.ExecuteNonQuery();
                }

                transaction.Commit();
            }
            catch (Exception exc)
            {
                if (transaction != null)
                    transaction.Rollback();
            }
            finally
            {
                if (transaction != null)
                    transaction.Dispose();
                connection.Close();
            }
        }

        private static void ProcessForthList()
        {
            List<Product> products = new List<Product>();

            var fileName = string.Format("{0}\\abc.xls", Directory.GetCurrentDirectory());
            var fileConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", fileName);

            DataTable priceTable;
            IEnumerable<DataRow> priceRows;
            using (var fileAdapter = new OleDbDataAdapter("SELECT * FROM [PriceC$]", fileConnectionString))
            {
                var fileDs = new DataSet();

                fileAdapter.Fill(fileDs, "PriceC");

                priceTable = fileDs.Tables["PriceC"];
                priceRows = priceTable.AsEnumerable();
            }

            List<PriceItem> priceItems = new List<PriceItem>();
            ProductType productType = new ProductType() { Id = Guid.NewGuid(), Name = "Фасады", UnitName = "м2"};

            // Populate materials
            foreach (var priceRow in priceRows)
            {
                string itemName = (string)priceRow[0];
                string description = priceRow[1] != DBNull.Value ? (string)priceRow[1] : string.Empty;
                double cost = double.Parse(priceRow[2].ToString());
                
                Product product = new Product()
                {
                    Id = Guid.NewGuid(),
                    Name = itemName,
                    Type = productType,
                    Description = description
                };
                var existingProduct = products.FirstOrDefault(p => p.GetHashCode() == product.GetHashCode());
                if (existingProduct == null)
                    products.Add(product);
                else
                    product = existingProduct;

                PriceItem priceItem = new PriceItem() { Id = Guid.NewGuid(), Cost = cost, Product = product };
                priceItems.Add(priceItem);
            }

            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
            connectionStringBuilder.DataSource = "(local)\\SQLEXPRESS";
            connectionStringBuilder.InitialCatalog = "ZOVNP";
            connectionStringBuilder.IntegratedSecurity = true;
            SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());

            SqlTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                SqlCommand command;

                command =
                    new SqlCommand(
                        "INSERT INTO [ZOVNP].[dbo].[ProductType] ([ID],[Name],[Description],[UnitName],[Property1Name],[Property2Name],[Property3Name]) VALUES (@id, @name, @description, @unitName, @property1Name, @property2Name, @property3Name)",
                        connection);
                command.Transaction = transaction;
                command.Parameters.AddWithValue("@id", productType.Id);
                command.Parameters.AddWithValue("@name", productType.Name);
                command.Parameters.AddWithValue("@description", "");
                command.Parameters.AddWithValue("@unitName", "");
                command.Parameters.AddWithValue("@property1Name", productType.Property1Name);
                command.Parameters.AddWithValue("@property2Name", productType.Property2Name);
                command.Parameters.AddWithValue("@property3Name", "");
                command.ExecuteNonQuery();

                foreach (var product in products)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[Product] ([ID],[Name],[Description],[ProductType_ID],[Producer],[Property1],[Property2],[Property3]) VALUES (@id, @name, @description, @productType, @producer, @property1, @property2, @property3)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", product.Id);
                    command.Parameters.AddWithValue("@name", product.Name);
                    command.Parameters.AddWithValue("@description", product.Description);
                    command.Parameters.AddWithValue("@productType", product.Type.Id);
                    command.Parameters.AddWithValue("@producer", "");
                    command.Parameters.AddWithValue("@property1", product.Property1);
                    command.Parameters.AddWithValue("@property2", product.Property2);
                    command.Parameters.AddWithValue("@property3", "");
                    command.ExecuteNonQuery();
                }

                foreach (var priceItem in priceItems)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[PriceItem] ([ID],[Price_ID],[Product_ID],[Material_ID],[Color],[Cost]) VALUES (@id, @priceId, @productId, @materialId, @color, @cost)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", priceItem.Id);
                    command.Parameters.AddWithValue("@priceId", new Guid("79A01695-098F-43D0-B3C6-731F398ED0F5"));
                    command.Parameters.AddWithValue("@productId", priceItem.Product.Id);
                    command.Parameters.AddWithValue("@materialId", priceItem.Material != null ? (object)priceItem.Material.Id : DBNull.Value);
                    command.Parameters.AddWithValue("@color", (object)priceItem.Color ?? DBNull.Value);
                    command.Parameters.AddWithValue("@cost", priceItem.Cost);
                    command.ExecuteNonQuery();
                }

                transaction.Commit();
            }
            catch (Exception exc)
            {
                if (transaction != null)
                    transaction.Rollback();
            }
            finally
            {
                if (transaction != null)
                    transaction.Dispose();
                connection.Close();
            }
        }

        private static void ProcessFifthList()
        {
            List<ProductType> productTypes = new List<ProductType>();
            List<Product> products = new List<Product>();

            var fileName = string.Format("{0}\\abc.xls", Directory.GetCurrentDirectory());
            var fileConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", fileName);

            DataTable priceTable;
            IEnumerable<DataRow> priceRows;
            using (var fileAdapter = new OleDbDataAdapter("SELECT * FROM [PriceD$]", fileConnectionString))
            {
                var fileDs = new DataSet();

                fileAdapter.Fill(fileDs, "PriceD");

                priceTable = fileDs.Tables["PriceD"];
                priceRows = priceTable.AsEnumerable();
            }

            List<PriceItem> priceItems = new List<PriceItem>();
            ProductType fasadiProductType = new ProductType() {Id = new Guid("789FE5FE-6EF7-4A93-A863-ABAF620CE2CA"), Name = "Фасады", NeedToInsert = false};
            ProductType pilyastriProductType = new ProductType() { Id = Guid.NewGuid(), Name = "Пилястры", UnitName = "шт" };
            ProductType balyasiniProductType = new ProductType() { Id = Guid.NewGuid(), Name = "Балясины", UnitName = "шт" };
            ProductType decorProductType = new ProductType() { Id = Guid.NewGuid(), Name = "Декоративные элементы", UnitName = "шт" };
            productTypes.Add(pilyastriProductType);
            productTypes.Add(balyasiniProductType);
            productTypes.Add(decorProductType);

            ProductType currentProductType = null;
            // Populate materials
            foreach (var priceRow in priceRows)
            {
                string itemName = priceRow[0].ToString();
                if(!string.IsNullOrWhiteSpace(itemName))
                {
                    string description = priceRow[1] != DBNull.Value ? (string) priceRow[1] : string.Empty;
                    double cost;
                    if (!double.TryParse(priceRow[2].ToString(), out cost) && string.IsNullOrWhiteSpace(description))
                    {
                        currentProductType = new ProductType() { Id = Guid.NewGuid(), Name = itemName, UnitName = "шт" };
                        productTypes.Add(currentProductType);
                        ProductType parentProductType = null;
                        if (itemName.Contains("ФАСАД") && !itemName.Contains("РЕШЕТКИ"))
                            parentProductType = fasadiProductType;
                        else if (itemName.Contains("ПИЛЯСТР"))
                            parentProductType = pilyastriProductType;
                        else if (itemName.Contains("БАЛЯСИН"))
                            parentProductType = balyasiniProductType;
                        else if (itemName.Contains("Декоративные элементы"))
                            parentProductType = decorProductType;
                        if (parentProductType != null)
                            currentProductType.ParentTypeId = parentProductType.Id;
                    }
                    else
                    {
                        if(description == "ТИП")
                            continue;

                        Product product = new Product()
                                              {
                                                  Id = Guid.NewGuid(),
                                                  Name = itemName,
                                                  Type = currentProductType,
                                                  Description = description
                                              };
                        var existingProduct = products.FirstOrDefault(p => p.GetHashCode() == product.GetHashCode());
                        if (existingProduct == null)
                            products.Add(product);
                        else
                            product = existingProduct;

                        PriceItem priceItem = new PriceItem() {Id = Guid.NewGuid(), Cost = cost, Product = product};
                        priceItems.Add(priceItem);
                    }
                }
            }

            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
            connectionStringBuilder.DataSource = "(local)\\SQLEXPRESS";
            connectionStringBuilder.InitialCatalog = "ZOVNP";
            connectionStringBuilder.IntegratedSecurity = true;
            SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());

            SqlTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                SqlCommand command;

                foreach (var productType in productTypes)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[ProductType] ([ID],[Name],[Description],[UnitName],[Property1Name],[Property2Name],[Property3Name],[ParentProductType_ID]) VALUES (@id, @name, @description, @unitName, @property1Name, @property2Name, @property3Name, @parentTypeId)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", productType.Id);
                    command.Parameters.AddWithValue("@name", productType.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.Parameters.AddWithValue("@unitName", productType.UnitName);
                    command.Parameters.AddWithValue("@property1Name", productType.Property1Name);
                    command.Parameters.AddWithValue("@property2Name", productType.Property2Name);
                    command.Parameters.AddWithValue("@property3Name", "");
                    command.Parameters.AddWithValue("@parentTypeId",
                                                    productType.ParentTypeId != Guid.Empty ? (object)productType.ParentTypeId : DBNull.Value);
                    command.ExecuteNonQuery();
                }

                foreach (var product in products)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[Product] ([ID],[Name],[Description],[ProductType_ID],[Producer],[Property1],[Property2],[Property3]) VALUES (@id, @name, @description, @productType, @producer, @property1, @property2, @property3)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", product.Id);
                    command.Parameters.AddWithValue("@name", product.Name);
                    command.Parameters.AddWithValue("@description", product.Description);
                    command.Parameters.AddWithValue("@productType", product.Type.Id);
                    command.Parameters.AddWithValue("@producer", "");
                    command.Parameters.AddWithValue("@property1", product.Property1);
                    command.Parameters.AddWithValue("@property2", product.Property2);
                    command.Parameters.AddWithValue("@property3", "");
                    command.ExecuteNonQuery();
                }

                foreach (var priceItem in priceItems)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[PriceItem] ([ID],[Price_ID],[Product_ID],[Material_ID],[Color],[Cost]) VALUES (@id, @priceId, @productId, @materialId, @color, @cost)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", priceItem.Id);
                    command.Parameters.AddWithValue("@priceId", new Guid("79A01695-098F-43D0-B3C6-731F398ED0F5"));
                    command.Parameters.AddWithValue("@productId", priceItem.Product.Id);
                    command.Parameters.AddWithValue("@materialId", priceItem.Material != null ? (object)priceItem.Material.Id : DBNull.Value);
                    command.Parameters.AddWithValue("@color", (object)priceItem.Color ?? DBNull.Value);
                    command.Parameters.AddWithValue("@cost", priceItem.Cost);
                    command.ExecuteNonQuery();
                }

                transaction.Commit();
            }
            catch (Exception exc)
            {
                if (transaction != null)
                    transaction.Rollback();
            }
            finally
            {
                if (transaction != null)
                    transaction.Dispose();
                connection.Close();
            }
        }

        private static void ProcessSixthList()
        {
            List<Product> products = new List<Product>();
            List<ProductType> productTypes = new List<ProductType>();
            List<Material> materials = new List<Material>();

            var fileName = string.Format("{0}\\abc.xls", Directory.GetCurrentDirectory());
            var fileConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", fileName);

            DataTable priceTable;
            IEnumerable<DataRow> priceRows;
            using (var fileAdapter = new OleDbDataAdapter("SELECT * FROM [PriceE$]", fileConnectionString))
            {
                var fileDs = new DataSet();

                fileAdapter.Fill(fileDs, "PriceE");

                priceTable = fileDs.Tables["PriceE"];
                priceRows = priceTable.AsEnumerable();
            }

            List<PriceItem> priceItems = new List<PriceItem>();
            ProductType parentProductType = new ProductType() { Id = Guid.NewGuid(), Name = "Массив Т" };
            productTypes.Add(parentProductType);
            Material olihaMaterial = new Material() {Id = Guid.NewGuid(), Name = "Ольха"};
            materials.Add(olihaMaterial);
            Material yasenMaterial = new Material() {Id = Guid.NewGuid(), Name = "Ясень"};
            materials.Add(yasenMaterial);

            ProductType currentProductType = null;

            // Populate materials
            foreach (var priceRow in priceRows)
            {
                string itemName = priceRow[0].ToString();
                if (!string.IsNullOrWhiteSpace(itemName))
                {
                    string unitName = priceRow[1].ToString();
                    if (string.IsNullOrWhiteSpace(unitName))
                    {
                        currentProductType = new ProductType() {Id = Guid.NewGuid(), Name = itemName.Trim(), ParentTypeId = parentProductType.Id, UnitName = "шт"};
                        productTypes.Add(currentProductType);
                    }
                    else
                    {
                        double cost1;
                        bool cost1Parsed = double.TryParse(priceRow[2].ToString(), out cost1);
                        double cost2;
                        bool cost2Parsed = double.TryParse(priceRow[3].ToString(), out cost2);
                        if(!cost1Parsed && !cost2Parsed)
                            continue;

                        Product product = new Product()
                                              {
                                                  Id = Guid.NewGuid(),
                                                  Name = itemName,
                                                  Type = currentProductType
                                              };
                        var existingProduct = products.FirstOrDefault(p => p.GetHashCode() == product.GetHashCode());
                        if (existingProduct == null)
                            products.Add(product);
                        else
                            product = existingProduct;

                        if (cost1Parsed)
                        {
                            PriceItem priceItem1 = new PriceItem() {Id = Guid.NewGuid(), Cost = cost1, Product = product, Material = olihaMaterial};
                            priceItems.Add(priceItem1);
                        }
                        if (cost2Parsed)
                        {
                            PriceItem priceItem2 = new PriceItem() {Id = Guid.NewGuid(), Cost = cost2, Product = product, Material = yasenMaterial};
                            priceItems.Add(priceItem2);
                        }
                    }
                }
            }

            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
            connectionStringBuilder.DataSource = "(local)\\SQLEXPRESS";
            connectionStringBuilder.InitialCatalog = "ZOVNP";
            connectionStringBuilder.IntegratedSecurity = true;
            SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());

            SqlTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                SqlCommand command;

                foreach (var material in materials)
                {
                    command = new SqlCommand("INSERT INTO [ZOVNP].[dbo].[Material] ([ID],[Name],[Description]) VALUES (@id,@name,@description)",
                                             connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", material.Id);
                    command.Parameters.AddWithValue("@name", material.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.ExecuteNonQuery();
                }

                foreach (var productType in productTypes)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[ProductType] ([ID],[Name],[Description],[UnitName],[Property1Name],[Property2Name],[Property3Name],[ParentProductType_ID]) VALUES (@id, @name, @description, @unitName, @property1Name, @property2Name, @property3Name, @parentTypeId)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", productType.Id);
                    command.Parameters.AddWithValue("@name", productType.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.Parameters.AddWithValue("@unitName", productType.UnitName ?? string.Empty);
                    command.Parameters.AddWithValue("@property1Name", productType.Property1Name);
                    command.Parameters.AddWithValue("@property2Name", productType.Property2Name);
                    command.Parameters.AddWithValue("@property3Name", "");
                    command.Parameters.AddWithValue("@parentTypeId",
                                                    productType.ParentTypeId != Guid.Empty ? (object)productType.ParentTypeId : DBNull.Value);
                    
                    command.ExecuteNonQuery();
                }

                foreach (var product in products)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[Product] ([ID],[Name],[Description],[ProductType_ID],[Producer],[Property1],[Property2],[Property3]) VALUES (@id, @name, @description, @productType, @producer, @property1, @property2, @property3)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", product.Id);
                    command.Parameters.AddWithValue("@name", product.Name);
                    command.Parameters.AddWithValue("@description", product.Description);
                    command.Parameters.AddWithValue("@productType", product.Type.Id);
                    command.Parameters.AddWithValue("@producer", "");
                    command.Parameters.AddWithValue("@property1", product.Property1);
                    command.Parameters.AddWithValue("@property2", product.Property2);
                    command.Parameters.AddWithValue("@property3", "");
                    command.ExecuteNonQuery();
                }

                foreach (var priceItem in priceItems)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[PriceItem] ([ID],[Price_ID],[Product_ID],[Material_ID],[Color],[Cost]) VALUES (@id, @priceId, @productId, @materialId, @color, @cost)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", priceItem.Id);
                    command.Parameters.AddWithValue("@priceId", new Guid("79A01695-098F-43D0-B3C6-731F398ED0F5"));
                    command.Parameters.AddWithValue("@productId", priceItem.Product.Id);
                    command.Parameters.AddWithValue("@materialId", priceItem.Material != null ? (object)priceItem.Material.Id : DBNull.Value);
                    command.Parameters.AddWithValue("@color", (object)priceItem.Color ?? DBNull.Value);
                    command.Parameters.AddWithValue("@cost", priceItem.Cost);
                    command.ExecuteNonQuery();
                }

                transaction.Commit();
            }
            catch (Exception exc)
            {
                if (transaction != null)
                    transaction.Rollback();
            }
            finally
            {
                if (transaction != null)
                    transaction.Dispose();
                connection.Close();
            }
        }

        private static void ProcessSeventhList()
        {
            List<Product> products = new List<Product>();
            Dictionary<string, ProductType> productTypes = new Dictionary<string, ProductType>();
            List<Material> materials = new List<Material>();

            var fileName = string.Format("{0}\\abc.xls", Directory.GetCurrentDirectory());
            var fileConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", fileName);

            DataTable priceTable;
            IEnumerable<DataRow> priceRows;
            using (var fileAdapter = new OleDbDataAdapter("SELECT * FROM [PriceF$]", fileConnectionString))
            {
                var fileDs = new DataSet();

                fileAdapter.Fill(fileDs, "PriceF");

                priceTable = fileDs.Tables["PriceF"];
                priceRows = priceTable.AsEnumerable();
            }

            List<PriceItem> priceItems = new List<PriceItem>();
            ProductType parentProductType = new ProductType() { Id = Guid.NewGuid(), Name = "Багет" };
            productTypes.Add(parentProductType.Name, parentProductType);

            ProductType currentProductType = null;

            // Populate materials
            foreach (var priceRow in priceRows)
            {
                string itemName = priceRow[0].ToString();
                if (!string.IsNullOrWhiteSpace(itemName))
                {
                    string property1 = priceRow[3].ToString();
                    double cost;
                    if (!double.TryParse(priceRow[4].ToString(), out cost))
                    {
                        if (!productTypes.ContainsKey(itemName))
                        {
                            currentProductType = new ProductType()
                                                     {
                                                         Id = Guid.NewGuid(),
                                                         Name = itemName.Trim(),
                                                         ParentTypeId = parentProductType.Id,
                                                         UnitName = "м.п., пиленый",
                                                         Property1Name = property1
                                                     };
                            productTypes.Add(currentProductType.Name, currentProductType);
                        }
                        else
                            currentProductType = productTypes[itemName];
                    }
                    else
                    {
                        string color = priceRow[1].ToString();
                        string description = priceRow[2].ToString();
                        Product product = new Product()
                                              {
                                                  Id = Guid.NewGuid(),
                                                  Name = itemName,
                                                  Type = currentProductType,
                                                  Property1 = property1,
                                                  Description = description
                                              };
                        var existingProduct = products.FirstOrDefault(p => p.GetHashCode() == product.GetHashCode());
                        if (existingProduct == null)
                            products.Add(product);
                        else
                            product = existingProduct;

                        PriceItem priceItem2 = new PriceItem() {Id = Guid.NewGuid(), Cost = cost, Product = product, Color = color};
                        priceItems.Add(priceItem2);
                    }
                }
            }

            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
            connectionStringBuilder.DataSource = "(local)\\SQLEXPRESS";
            connectionStringBuilder.InitialCatalog = "ZOVNP";
            connectionStringBuilder.IntegratedSecurity = true;
            SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());

            SqlTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                SqlCommand command;

                foreach (var material in materials)
                {
                    command = new SqlCommand("INSERT INTO [ZOVNP].[dbo].[Material] ([ID],[Name],[Description]) VALUES (@id,@name,@description)",
                                             connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", material.Id);
                    command.Parameters.AddWithValue("@name", material.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.ExecuteNonQuery();
                }

                foreach (var productType in productTypes)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[ProductType] ([ID],[Name],[Description],[UnitName],[Property1Name],[Property2Name],[Property3Name],[ParentProductType_ID]) VALUES (@id, @name, @description, @unitName, @property1Name, @property2Name, @property3Name, @parentTypeId)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", productType.Value.Id);
                    command.Parameters.AddWithValue("@name", productType.Value.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.Parameters.AddWithValue("@unitName", productType.Value.UnitName ?? string.Empty);
                    command.Parameters.AddWithValue("@property1Name", productType.Value.Property1Name);
                    command.Parameters.AddWithValue("@property2Name", productType.Value.Property2Name);
                    command.Parameters.AddWithValue("@property3Name", "");
                    command.Parameters.AddWithValue("@parentTypeId",
                                                    productType.Value.ParentTypeId != Guid.Empty ? (object)productType.Value.ParentTypeId : DBNull.Value);

                    command.ExecuteNonQuery();
                }

                foreach (var product in products)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[Product] ([ID],[Name],[Description],[ProductType_ID],[Producer],[Property1],[Property2],[Property3]) VALUES (@id, @name, @description, @productType, @producer, @property1, @property2, @property3)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", product.Id);
                    command.Parameters.AddWithValue("@name", product.Name);
                    command.Parameters.AddWithValue("@description", product.Description);
                    command.Parameters.AddWithValue("@productType", product.Type.Id);
                    command.Parameters.AddWithValue("@producer", "");
                    command.Parameters.AddWithValue("@property1", product.Property1);
                    command.Parameters.AddWithValue("@property2", product.Property2);
                    command.Parameters.AddWithValue("@property3", "");
                    command.ExecuteNonQuery();
                }

                foreach (var priceItem in priceItems)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[PriceItem] ([ID],[Price_ID],[Product_ID],[Material_ID],[Color],[Cost]) VALUES (@id, @priceId, @productId, @materialId, @color, @cost)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", priceItem.Id);
                    command.Parameters.AddWithValue("@priceId", new Guid("79A01695-098F-43D0-B3C6-731F398ED0F5"));
                    command.Parameters.AddWithValue("@productId", priceItem.Product.Id);
                    command.Parameters.AddWithValue("@materialId", priceItem.Material != null ? (object)priceItem.Material.Id : DBNull.Value);
                    command.Parameters.AddWithValue("@color", (object)priceItem.Color ?? DBNull.Value);
                    command.Parameters.AddWithValue("@cost", priceItem.Cost);
                    command.ExecuteNonQuery();
                }

                transaction.Commit();
            }
            catch (Exception exc)
            {
                if (transaction != null)
                    transaction.Rollback();
            }
            finally
            {
                if (transaction != null)
                    transaction.Dispose();
                connection.Close();
            }
        }

        private static void ProcessEighthList()
        {
            List<Product> products = new List<Product>();

            var fileName = string.Format("{0}\\abc.xls", Directory.GetCurrentDirectory());
            var fileConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", fileName);

            DataTable priceTable;
            IEnumerable<DataRow> priceRows;
            using (var fileAdapter = new OleDbDataAdapter("SELECT * FROM [PriceG$]", fileConnectionString))
            {
                var fileDs = new DataSet();

                fileAdapter.Fill(fileDs, "PriceG");

                priceTable = fileDs.Tables["PriceG"];
                priceRows = priceTable.AsEnumerable();
            }

            List<PriceItem> priceItems = new List<PriceItem>();
            ProductType currentProductType = new ProductType() {Id = new Guid("4BA43B91-1A98-4AAD-AC17-46B70E541E46"), Name = "Декоративные элементы"};
            
            // Populate materials
            foreach (var priceRow in priceRows)
            {
                string itemName = priceRow[0].ToString();
                string color = priceRow[1].ToString();
                string description = priceRow[2].ToString();
                double cost = double.Parse(priceRow[4].ToString());

                Product product = new Product()
                                      {
                                          Id = Guid.NewGuid(),
                                          Name = itemName,
                                          Type = currentProductType,
                                          Description = description
                                      };

                var existingProduct = products.FirstOrDefault(p => p.GetHashCode() == product.GetHashCode());
                if (existingProduct == null)
                    products.Add(product);
                else
                    product = existingProduct;

                PriceItem priceItem2 = new PriceItem() {Id = Guid.NewGuid(), Cost = cost, Product = product, Color = color};
                priceItems.Add(priceItem2);
            }

            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
            connectionStringBuilder.DataSource = "(local)\\SQLEXPRESS";
            connectionStringBuilder.InitialCatalog = "ZOVNP";
            connectionStringBuilder.IntegratedSecurity = true;
            SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());

            SqlTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                SqlCommand command;

                foreach (var product in products)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[Product] ([ID],[Name],[Description],[ProductType_ID],[Producer],[Property1],[Property2],[Property3]) VALUES (@id, @name, @description, @productType, @producer, @property1, @property2, @property3)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", product.Id);
                    command.Parameters.AddWithValue("@name", product.Name);
                    command.Parameters.AddWithValue("@description", product.Description);
                    command.Parameters.AddWithValue("@productType", product.Type.Id);
                    command.Parameters.AddWithValue("@producer", "");
                    command.Parameters.AddWithValue("@property1", product.Property1);
                    command.Parameters.AddWithValue("@property2", product.Property2);
                    command.Parameters.AddWithValue("@property3", "");
                    command.ExecuteNonQuery();
                }

                foreach (var priceItem in priceItems)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[PriceItem] ([ID],[Price_ID],[Product_ID],[Material_ID],[Color],[Cost]) VALUES (@id, @priceId, @productId, @materialId, @color, @cost)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", priceItem.Id);
                    command.Parameters.AddWithValue("@priceId", new Guid("79A01695-098F-43D0-B3C6-731F398ED0F5"));
                    command.Parameters.AddWithValue("@productId", priceItem.Product.Id);
                    command.Parameters.AddWithValue("@materialId", priceItem.Material != null ? (object)priceItem.Material.Id : DBNull.Value);
                    command.Parameters.AddWithValue("@color", (object)priceItem.Color ?? DBNull.Value);
                    command.Parameters.AddWithValue("@cost", priceItem.Cost);
                    command.ExecuteNonQuery();
                }

                transaction.Commit();
            }
            catch (Exception exc)
            {
                if (transaction != null)
                    transaction.Rollback();
            }
            finally
            {
                if (transaction != null)
                    transaction.Dispose();
                connection.Close();
            }
        }

        private static void ProcessNinthList()
        {
            List<Product> products = new List<Product>();
            Dictionary<string, Guid> existingMaterials = new Dictionary<string, Guid>();
            List<Material> materials = new List<Material>();
            
            var fileName = string.Format("{0}\\abc.xls", Directory.GetCurrentDirectory());
            var fileConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", fileName);

            DataTable priceTable;
            IEnumerable<DataRow> priceRows;
            using (var fileAdapter = new OleDbDataAdapter("SELECT * FROM [PriceH$]", fileConnectionString))
            {
                var fileDs = new DataSet();

                fileAdapter.Fill(fileDs, "PriceH");

                priceTable = fileDs.Tables["PriceH"];
                priceRows = priceTable.AsEnumerable();
            }

            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
            connectionStringBuilder.DataSource = "(local)\\SQLEXPRESS";
            connectionStringBuilder.InitialCatalog = "ZOVNP";
            connectionStringBuilder.IntegratedSecurity = true;
            SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());
            connection.Open();
            DataTable materialsTable = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter("select ID, Name from Material", connection);
            adapter.Fill(materialsTable);
            connection.Close();
            materialsTable.AsEnumerable().ForEach(r => existingMaterials.Add(r.Field<string>("Name"), r.Field<Guid>("ID")));
            materialsTable.Dispose();
            adapter.Dispose();

            List<PriceItem> priceItems = new List<PriceItem>();
            ProductType productType = new ProductType() { Id = Guid.NewGuid(), Name = "Ножки мебельные" };

            foreach (var priceRow in priceRows)
            {
                string itemName = priceRow[0].ToString();
                string materialName = priceRow[1].ToString();
                string color = priceRow[2].ToString();
                string description = priceRow[3].ToString();
                double cost = double.Parse(priceRow[4].ToString());
                Product product = new Product()
                                      {
                                          Id = Guid.NewGuid(),
                                          Name = itemName,
                                          Type = productType,
                                          Description = description
                                      };
                var existingProduct = products.FirstOrDefault(p => p.GetHashCode() == product.GetHashCode());
                if (existingProduct == null)
                    products.Add(product);
                else
                    product = existingProduct;

                Material material = null;
                if (!string.IsNullOrWhiteSpace(materialName))
                {
                    if (existingMaterials.ContainsKey(materialName))
                        material = new Material() {Id = existingMaterials[materialName], Name = materialName, NeedToInsert = false};
                    else
                        material = new Material() {Id = Guid.NewGuid(), Name = materialName};

                    var existingMaterial = materials.FirstOrDefault(p => p.GetHashCode() == material.GetHashCode());
                    if (existingMaterial == null)
                        materials.Add(material);
                    else
                        material = existingMaterial;
                }

                PriceItem priceItem2 = new PriceItem() {Id = Guid.NewGuid(), Cost = cost, Product = product, Color = color, Material = material};
                priceItems.Add(priceItem2);
            }


            SqlTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                SqlCommand command;

                foreach (var material in materials.Where(m => m.NeedToInsert))
                {
                    command = new SqlCommand("INSERT INTO [ZOVNP].[dbo].[Material] ([ID],[Name],[Description]) VALUES (@id,@name,@description)",
                                             connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", material.Id);
                    command.Parameters.AddWithValue("@name", material.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.ExecuteNonQuery();
                }

                //foreach (var productType in productTypes)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[ProductType] ([ID],[Name],[Description],[UnitName],[Property1Name],[Property2Name],[Property3Name],[ParentProductType_ID]) VALUES (@id, @name, @description, @unitName, @property1Name, @property2Name, @property3Name, @parentTypeId)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", productType.Id);
                    command.Parameters.AddWithValue("@name", productType.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.Parameters.AddWithValue("@unitName", productType.UnitName ?? string.Empty);
                    command.Parameters.AddWithValue("@property1Name", productType.Property1Name);
                    command.Parameters.AddWithValue("@property2Name", productType.Property2Name);
                    command.Parameters.AddWithValue("@property3Name", "");
                    command.Parameters.AddWithValue("@parentTypeId",
                                                    productType.ParentTypeId != Guid.Empty ? (object)productType.ParentTypeId : DBNull.Value);

                    command.ExecuteNonQuery();
                }

                foreach (var product in products)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[Product] ([ID],[Name],[Description],[ProductType_ID],[Producer],[Property1],[Property2],[Property3]) VALUES (@id, @name, @description, @productType, @producer, @property1, @property2, @property3)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", product.Id);
                    command.Parameters.AddWithValue("@name", product.Name);
                    command.Parameters.AddWithValue("@description", product.Description);
                    command.Parameters.AddWithValue("@productType", product.Type.Id);
                    command.Parameters.AddWithValue("@producer", "");
                    command.Parameters.AddWithValue("@property1", product.Property1);
                    command.Parameters.AddWithValue("@property2", product.Property2);
                    command.Parameters.AddWithValue("@property3", "");
                    command.ExecuteNonQuery();
                }

                foreach (var priceItem in priceItems)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[PriceItem] ([ID],[Price_ID],[Product_ID],[Material_ID],[Color],[Cost]) VALUES (@id, @priceId, @productId, @materialId, @color, @cost)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", priceItem.Id);
                    command.Parameters.AddWithValue("@priceId", new Guid("79A01695-098F-43D0-B3C6-731F398ED0F5"));
                    command.Parameters.AddWithValue("@productId", priceItem.Product.Id);
                    command.Parameters.AddWithValue("@materialId", priceItem.Material != null ? (object)priceItem.Material.Id : DBNull.Value);
                    command.Parameters.AddWithValue("@color", (object)priceItem.Color ?? DBNull.Value);
                    command.Parameters.AddWithValue("@cost", priceItem.Cost);
                    command.ExecuteNonQuery();
                }

                transaction.Commit();
            }
            catch (Exception exc)
            {
                if (transaction != null)
                    transaction.Rollback();
            }
            finally
            {
                if (transaction != null)
                    transaction.Dispose();
                connection.Close();
            }
        }

        private static void ProcessTenthList()
        {
            List<Product> products = new List<Product>();
            List<Material> materials = new List<Material>();

            var fileName = string.Format("{0}\\abc.xls", Directory.GetCurrentDirectory());
            var fileConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", fileName);

            DataTable priceTable;
            IEnumerable<DataRow> priceRows;
            using (var fileAdapter = new OleDbDataAdapter("SELECT * FROM [PriceI$]", fileConnectionString))
            {
                var fileDs = new DataSet();

                fileAdapter.Fill(fileDs, "PriceI");

                priceTable = fileDs.Tables["PriceI"];
                priceRows = priceTable.AsEnumerable();
            }

            List<PriceItem> priceItems = new List<PriceItem>();
            ProductType productType = new ProductType() { Id = Guid.NewGuid(), Name = "Цоколь" };

            foreach (var priceRow in priceRows)
            {
                string itemName = priceRow[0].ToString();
                string producerName = priceRow[1].ToString();
                string color = priceRow[2].ToString();
                double cost = double.Parse(priceRow[3].ToString());
                Product product = new Product()
                {
                    Id = Guid.NewGuid(),
                    Name = itemName,
                    Type = productType,
                    Producer = producerName
                };
                var existingProduct = products.FirstOrDefault(p => p.GetHashCode() == product.GetHashCode());
                if (existingProduct == null)
                    products.Add(product);
                else
                    product = existingProduct;
                
                PriceItem priceItem2 = new PriceItem() { Id = Guid.NewGuid(), Cost = cost, Product = product, Color = color };
                priceItems.Add(priceItem2);
            }

            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
            connectionStringBuilder.DataSource = "(local)\\SQLEXPRESS";
            connectionStringBuilder.InitialCatalog = "ZOVNP";
            connectionStringBuilder.IntegratedSecurity = true;
            SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());
            
            SqlTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                SqlCommand command;

                foreach (var material in materials.Where(m => m.NeedToInsert))
                {
                    command = new SqlCommand("INSERT INTO [ZOVNP].[dbo].[Material] ([ID],[Name],[Description]) VALUES (@id,@name,@description)",
                                             connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", material.Id);
                    command.Parameters.AddWithValue("@name", material.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.ExecuteNonQuery();
                }

                //foreach (var productType in productTypes)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[ProductType] ([ID],[Name],[Description],[UnitName],[Property1Name],[Property2Name],[Property3Name],[ParentProductType_ID]) VALUES (@id, @name, @description, @unitName, @property1Name, @property2Name, @property3Name, @parentTypeId)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", productType.Id);
                    command.Parameters.AddWithValue("@name", productType.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.Parameters.AddWithValue("@unitName", productType.UnitName ?? string.Empty);
                    command.Parameters.AddWithValue("@property1Name", productType.Property1Name);
                    command.Parameters.AddWithValue("@property2Name", productType.Property2Name);
                    command.Parameters.AddWithValue("@property3Name", "");
                    command.Parameters.AddWithValue("@parentTypeId",
                                                    productType.ParentTypeId != Guid.Empty ? (object)productType.ParentTypeId : DBNull.Value);

                    command.ExecuteNonQuery();
                }

                foreach (var product in products)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[Product] ([ID],[Name],[Description],[ProductType_ID],[Producer],[Property1],[Property2],[Property3]) VALUES (@id, @name, @description, @productType, @producer, @property1, @property2, @property3)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", product.Id);
                    command.Parameters.AddWithValue("@name", product.Name);
                    command.Parameters.AddWithValue("@description", product.Description);
                    command.Parameters.AddWithValue("@productType", product.Type.Id);
                    command.Parameters.AddWithValue("@producer", product.Producer);
                    command.Parameters.AddWithValue("@property1", product.Property1);
                    command.Parameters.AddWithValue("@property2", product.Property2);
                    command.Parameters.AddWithValue("@property3", "");
                    command.ExecuteNonQuery();
                }

                foreach (var priceItem in priceItems)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[PriceItem] ([ID],[Price_ID],[Product_ID],[Material_ID],[Color],[Cost]) VALUES (@id, @priceId, @productId, @materialId, @color, @cost)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", priceItem.Id);
                    command.Parameters.AddWithValue("@priceId", new Guid("79A01695-098F-43D0-B3C6-731F398ED0F5"));
                    command.Parameters.AddWithValue("@productId", priceItem.Product.Id);
                    command.Parameters.AddWithValue("@materialId", priceItem.Material != null ? (object)priceItem.Material.Id : DBNull.Value);
                    command.Parameters.AddWithValue("@color", (object)priceItem.Color ?? DBNull.Value);
                    command.Parameters.AddWithValue("@cost", priceItem.Cost);
                    command.ExecuteNonQuery();
                }

                transaction.Commit();
            }
            catch (Exception exc)
            {
                if (transaction != null)
                    transaction.Rollback();
            }
            finally
            {
                if (transaction != null)
                    transaction.Dispose();
                connection.Close();
            }
        }

        private static void ProcessEleventhList()
        {
            List<Product> products = new List<Product>();
            List<Material> materials = new List<Material>();

            var fileName = string.Format("{0}\\abc.xls", Directory.GetCurrentDirectory());
            var fileConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", fileName);

            DataTable priceTable;
            IEnumerable<DataRow> priceRows;
            using (var fileAdapter = new OleDbDataAdapter("SELECT * FROM [PriceJ$]", fileConnectionString))
            {
                var fileDs = new DataSet();

                fileAdapter.Fill(fileDs, "PriceJ");

                priceTable = fileDs.Tables["PriceJ"];
                priceRows = priceTable.AsEnumerable();
            }

            List<PriceItem> priceItems = new List<PriceItem>();
            ProductType productType = new ProductType() { Id = Guid.NewGuid(), Name = "Карго" };

            foreach (var priceRow in priceRows)
            {
                string itemName = priceRow[0].ToString();
                string color = priceRow[1].ToString();
                string producerName = priceRow[2].ToString();
                double cost = double.Parse(priceRow[3].ToString());
                Product product = new Product()
                {
                    Id = Guid.NewGuid(),
                    Name = itemName,
                    Type = productType,
                    Producer = producerName
                };
                var existingProduct = products.FirstOrDefault(p => p.GetHashCode() == product.GetHashCode());
                if (existingProduct == null)
                    products.Add(product);
                else
                    product = existingProduct;

                PriceItem priceItem2 = new PriceItem() { Id = Guid.NewGuid(), Cost = cost, Product = product, Color = color };
                priceItems.Add(priceItem2);
            }

            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
            connectionStringBuilder.DataSource = "(local)\\SQLEXPRESS";
            connectionStringBuilder.InitialCatalog = "ZOVNP";
            connectionStringBuilder.IntegratedSecurity = true;
            SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());

            SqlTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                SqlCommand command;

                foreach (var material in materials.Where(m => m.NeedToInsert))
                {
                    command = new SqlCommand("INSERT INTO [ZOVNP].[dbo].[Material] ([ID],[Name],[Description]) VALUES (@id,@name,@description)",
                                             connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", material.Id);
                    command.Parameters.AddWithValue("@name", material.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.ExecuteNonQuery();
                }

                //foreach (var productType in productTypes)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[ProductType] ([ID],[Name],[Description],[UnitName],[Property1Name],[Property2Name],[Property3Name],[ParentProductType_ID]) VALUES (@id, @name, @description, @unitName, @property1Name, @property2Name, @property3Name, @parentTypeId)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", productType.Id);
                    command.Parameters.AddWithValue("@name", productType.Name);
                    command.Parameters.AddWithValue("@description", "");
                    command.Parameters.AddWithValue("@unitName", productType.UnitName ?? string.Empty);
                    command.Parameters.AddWithValue("@property1Name", productType.Property1Name);
                    command.Parameters.AddWithValue("@property2Name", productType.Property2Name);
                    command.Parameters.AddWithValue("@property3Name", "");
                    command.Parameters.AddWithValue("@parentTypeId",
                                                    productType.ParentTypeId != Guid.Empty ? (object)productType.ParentTypeId : DBNull.Value);

                    command.ExecuteNonQuery();
                }

                foreach (var product in products)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[Product] ([ID],[Name],[Description],[ProductType_ID],[Producer],[Property1],[Property2],[Property3]) VALUES (@id, @name, @description, @productType, @producer, @property1, @property2, @property3)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", product.Id);
                    command.Parameters.AddWithValue("@name", product.Name);
                    command.Parameters.AddWithValue("@description", product.Description);
                    command.Parameters.AddWithValue("@productType", product.Type.Id);
                    command.Parameters.AddWithValue("@producer", product.Producer);
                    command.Parameters.AddWithValue("@property1", product.Property1);
                    command.Parameters.AddWithValue("@property2", product.Property2);
                    command.Parameters.AddWithValue("@property3", "");
                    command.ExecuteNonQuery();
                }

                foreach (var priceItem in priceItems)
                {
                    command =
                        new SqlCommand(
                            "INSERT INTO [ZOVNP].[dbo].[PriceItem] ([ID],[Price_ID],[Product_ID],[Material_ID],[Color],[Cost]) VALUES (@id, @priceId, @productId, @materialId, @color, @cost)",
                            connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@id", priceItem.Id);
                    command.Parameters.AddWithValue("@priceId", new Guid("79A01695-098F-43D0-B3C6-731F398ED0F5"));
                    command.Parameters.AddWithValue("@productId", priceItem.Product.Id);
                    command.Parameters.AddWithValue("@materialId", priceItem.Material != null ? (object)priceItem.Material.Id : DBNull.Value);
                    command.Parameters.AddWithValue("@color", (object)priceItem.Color ?? DBNull.Value);
                    command.Parameters.AddWithValue("@cost", priceItem.Cost);
                    command.ExecuteNonQuery();
                }

                transaction.Commit();
            }
            catch (Exception exc)
            {
                if (transaction != null)
                    transaction.Rollback();
            }
            finally
            {
                if (transaction != null)
                    transaction.Dispose();
                connection.Close();
            }
        }
    }
}
